package com.dinghui.salary.impl;

import com.alibaba.dubbo.config.annotation.Reference;
import com.alibaba.dubbo.config.annotation.Service;
import com.dinghui.salary.enums.ResponseStatusEnum;
import com.dinghui.salary.exception.CommonException;
import com.dinghui.salary.mapper.MonthSalaryMapper;
import com.dinghui.salary.mapper.MouthTypeMapper;
import com.dinghui.salary.mapper.SalaryMapper;
import com.dinghui.salary.mapper.SalaryTypeMapper;
import com.dinghui.salary.pojo.*;
import com.dinghui.salary.pojo.dto.MouthTypeDTO;
import com.dinghui.salary.pojo.dto.SalaryStructureDTO;
import com.dinghui.salary.pojo.dto.SearchMouthTypeDTO;
import com.dinghui.salary.pojo.vo.MouthTypeDetailVO;
import com.dinghui.salary.pojo.vo.MouthTypeVO;
import com.dinghui.salary.pojo.vo.SalaryStructureVO;
import com.dinghui.salary.pojo.vo.SelectSalaryTypeVO;
import com.dinghui.salary.service.MouthTypeService;
import com.dinghui.salary.service.SalaryTypeService;
import com.dinghui.salary.utils.DateUtil;
import com.dinghui.salary.utils.GetUUID;
import com.dinghui.salary.utils.JDBC;
import com.dinghui.salary.utils.PagedGridResult;
import com.github.pagehelper.PageHelper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;


/**
 * @author tanrentao
 * @date 2021-09-06
 * @desc
 */
@Slf4j
@Service(version = "2.0.0")
public class MouthTypeServiceImpl extends BaseService implements MouthTypeService {

    @Autowired

    private MouthTypeMapper mouthTypeMapper;

    @Autowired
    private SalaryTypeMapper salaryTypeMapper;

    @Autowired
    private MonthSalaryMapper monthSalaryMapper;

    @Override
    public PagedGridResult findList(SearchMouthTypeDTO dto) {
        Example example = new Example(MouthType.class);
        Example.Criteria criteria = example.createCriteria();
        example.orderBy("time").desc();
        if (StringUtils.isNotBlank(dto.getTitle())) {
            criteria.andLike("title", "%" + dto.getTitle() + "%");
        }
        criteria.andEqualTo("isDelete",  0);
        PageHelper.startPage(dto.getPage(), dto.getPageSize());
        List<MouthType> mouthTypeList = mouthTypeMapper.selectByExample(example);

//        List<MouthTypeVO> list = mouthTypeList.stream()
//                .map(mouthType -> {
//                    MouthTypeVO mouthTypeVO = new MouthTypeVO();
//                    BeanUtils.copyProperties(mouthType, mouthTypeVO);
//                    return mouthTypeVO;
//                }).collect(Collectors.toList());
        return setterPagedGrid(mouthTypeList, dto.getPage());
    }

    @Override
    public List<MouthTypeDetailVO> findById(String id) {
        List<MouthTypeDetailVO> list = mouthTypeMapper.selectByIdList(id);
        return list;
    }


    @Override
    public PagedGridResult selectSalaryStructureList(SalaryStructureDTO dto) {
        PageHelper.startPage(dto.getPage(), dto.getPageSize());
        List<SalaryStructureVO> list =  salaryTypeMapper.selectSalaryStructureList(dto.getMonthTypeId());
        return setterPagedGrid(list, dto.getPage());
    }


    @Transactional
    @Override
    public void create(MouthTypeDTO dto, User user) {
        String time = dto.getTime();
        // 1.判断数据是否存在
        Example example = new Example(MouthType.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("time", time);
        MouthType isExistMouthType = mouthTypeMapper.selectOneByExample(example);
        if (ObjectUtils.isNotEmpty(isExistMouthType)) {
            CommonException.display(ResponseStatusEnum.DATA_EXIST_ERROR);
        }

        // 2.月类型表插入数据
        MouthType mouthType = new MouthType();
        BeanUtils.copyProperties(dto, mouthType);
        String mouthTypeId = GetUUID.getUUID();
        mouthType.setId(mouthTypeId);
        mouthType.setTitle(time+"工资表");

        String[] times = time.split("-");
        String tableName = times[0] + times[1] + "_"+"salary";
        mouthType.setTableName(tableName);
        mouthType.setCreateBy(user.getName());
        mouthType.setCreateTime(new Date());
        mouthType.setUpdateBy(user.getName());
        mouthType.setUpdateTime(new Date());
        mouthTypeMapper.insertSelective(mouthType);

        // 3.月类型和工资类型表插入数据
//        List<String> ids = dto.getIds();
//        ids.forEach(id -> {
//            Example salaryTypeExample = new Example(SalaryType.class);
//            salaryTypeExample.createCriteria().andEqualTo("id", id);
//            SalaryType salaryType = salaryTypeMapper.selectOneByExample(salaryTypeExample);
//
//            MonthSalary monthSalary = new MonthSalary();
//            monthSalary.setId(GetUUID.getUUID());
//            monthSalary.setMonthTypeId(mouthTypeId);
//            monthSalary.setSalaryTypeId(salaryType.getId());
//            monthSalaryMapper.insertSelective(monthSalary);
//        });
        List<SalaryType> list = salaryTypeMapper.selectSalaryTypeByIds(dto.getIds());
        list.forEach(salaryType -> {
            MonthSalary monthSalary = new MonthSalary();
            monthSalary.setId(GetUUID.getUUID());
            monthSalary.setMonthTypeId(mouthTypeId);
            monthSalary.setSalaryTypeId(salaryType.getId());
            monthSalaryMapper.insertSelective(monthSalary);
        });

        // 4.生成表
        boolean result = createTable(time);
        // 5.插入教师姓名、身份证等基础字段
        if (result) {
            insertSalaryTableData(mouthTypeId, tableName, time, user.getXXDM());
        }
    }

    public boolean createTable(String time) {
        Connection conn = null;
        PreparedStatement st = null;
        List<SelectSalaryTypeVO> list = salaryTypeMapper.selectList(time);
        if (list.size() > 0) {
            try {
                String sql = "create table "+ list.get(0).getTable_name()+" (id varchar(64) primary key not null";
                if(list!=null&&list.size()>0){
                    sql+=",";
                    sql+="name varchar(64),";
                    sql+="id_number varchar(64),";
                    sql+="time varchar(20),";
                    sql+="should_pay decimal(10,3),";
                    sql+="real_wages decimal(10,3),";
                    sql+="month_type_id varchar(60),";
                    sql+="is_delete bit(1),";
                    sql+="create_by varchar(20),";
                    sql+="create_time varchar(60),";
                    sql+="update_by varchar(20),";
                    sql+="update_time varchar(60),";
                    int length = list.size();
                    for(int i =0 ;i<length;i++){
                        //添加字段
                        sql+=list.get(i).getName()+" " +list.get(i).getField_type() +"("+ list.get(i).getField_size()+",3"+")";
                        //防止最后一个,
                        if(i<length-1){
                            sql+=",";
                        }
                    }
                }
                //拼凑完 建表语句 设置默认字符集
                sql+=")DEFAULT CHARSET=utf8;";
                System.out.println("建表语句是："+sql);
                String ss = sql;
                conn = JDBC.getConnection("dh_salary");
                conn.setAutoCommit(false);//false为sql操作手动提交，true为每次对数据库操作时自动提交
                st = conn.prepareStatement(sql);
                st.executeUpdate(sql);
                conn.commit();//因为AutoCommit设置为false所以要手动提交
                return true;
            } catch (SQLException e) {
                try {
                    conn.rollback();//回滚
                } catch (SQLException e1) {
                    e.printStackTrace();
                }
            } finally {
                JDBC.closeConnection(conn);
                JDBC.closeStatement(st);
            }
        }
        return false;
    }

    public void insertSalaryTableData(String mouthTypeId, String tableName, String time,  Integer xxdm) {
        // 1.查询学校老师
        List<User> list = mouthTypeMapper.selectTeacherListByxxdm(xxdm);

        // 2.工资表插入基本数据信息
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs =null;
        try {
            conn= JDBC.getConnection("dh_salary");
            //conn.setAutoCommit(false);
            for (User user: list) {
                // 根据身份证号、时间查询数据是否存在,不存在则插入表中
                String id = GetUUID.getUUID();
                String userName = user.getName();
                String idNumber = user.getId_number();
                String selectSQL = MessageFormat.format("select * from {0} where id_number = ''{1}'' and time = ''{2}''", tableName, idNumber, time);

                st = conn.prepareStatement(selectSQL);
                rs = st.executeQuery();
                if (!rs.next()) {
                    // 插入数据库
                    //String insertSQL = "insert into " + tableName + "(id, id_number, name, time, month_type_id, is_delete) values ('"+id+"',"+idNumber+",'"+userName+"','"+time+"','"+mouthTypeId+"',"+"0"+")" ;
                    String insertSQL = MessageFormat.format("insert into {0} (id, id_number, name, time, month_type_id, is_delete) values (''{1}'',''{2}'',''{3}'',''{4}'',''{5}'',0)", tableName, id, idNumber, userName, time, mouthTypeId);
                    st = conn.prepareStatement(insertSQL);
                    st.executeUpdate();
                }
            }
            //conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
//            try {
//                conn.rollback();
//            } catch (SQLException e1) {
//                e.printStackTrace();
//            }
        } finally {
            JDBC.closeResultSet(rs);
            JDBC.closeStatement(st);
            JDBC.closeConnection(conn);
        }
    }

    @Override
    public int update(MouthTypeDTO dto, User user) {
        // 1.判断数据是否存在
        Example example = new Example(MouthType1.class);
        Example.Criteria criteria = example.createCriteria();
        /*criteria.andEqualTo("year", dto.getYear());
        criteria.andEqualTo("mouth", dto.getMouth());*/
        MouthType isExistMouthType = mouthTypeMapper.selectOneByExample(example);
        if (ObjectUtils.isNotEmpty(isExistMouthType)) {
            CommonException.display(ResponseStatusEnum.DATA_EXIST_ERROR);
        }

        MouthType mouthType = new MouthType();
        BeanUtils.copyProperties(dto, mouthType);
        mouthType.setUpdateBy(user.getName());
        mouthType.setUpdateTime(new Date());

        Example selectExample = new Example(MonthSalary.class);
        selectExample.createCriteria().andEqualTo("id", dto.getId());

        int result = mouthTypeMapper.updateByPrimaryKeySelective(mouthType);
        return result;
    }

    @Override
    public int delete(String id) {
        MouthType mouthType = new MouthType();
        mouthType.setIsDelete(true);

        Example mouthTypeExample = new Example(MouthType.class);
        String tableName = "";
        if (StringUtils.isNotEmpty(id)) {
            mouthTypeExample.createCriteria().andEqualTo("id", id);
            mouthType = mouthTypeMapper.selectOneByExample(mouthTypeExample);
            if (ObjectUtils.isNotEmpty(mouthType)) {
                tableName = mouthType.getTableName();
            }
        }
//        int result = mouthTypeMapper.updateByExampleSelective(mouthType,example);
        int result = mouthTypeMapper.delete(mouthType);
        if(result == 1){
            int results = mouthTypeMapper.drop(tableName);
        }

        return result;
    }

//    @Transactional
//    @Override
//    public void generate(String id, User user) {
//        // 1.查询学校老师
//        List<User> list = mouthTypeMapper.selectTeacherListByxxdm(user.getXXDM());
//
//        // 2.获取当前月的工资结构信息
//        Example MouthTypeExample = new Example(MouthType1.class);
//        MouthTypeExample.createCriteria().andEqualTo("id", id);
//        MouthType mouthType = mouthTypeMapper.selectOneByExample(MouthTypeExample);
//
//        // 3.工资表插入基本数据信息
//        list.forEach(user1 -> {
//            // 根据工号、年、月查询数据是否存在,不存在则插入表中
//            Example salaryExample = new Example(Salary.class);
//            Example.Criteria criteria = salaryExample.createCriteria();
//            criteria.andEqualTo("jobNumber", user1.getId_number());
//            criteria.andEqualTo("year", mouthType.getYear());
//            criteria.andEqualTo("month", mouthType.getMouth());
//            Salary isExistSalary = salaryMapper.selectOneByExample(salaryExample);
//            if (ObjectUtils.isEmpty(isExistSalary)) {
//                Salary salary = new Salary();
//                salary.setId(GetUUID.getUUID());
//                salary.setName(user1.getName());
//                salary.setJobNumber(user1.getId_number());
//                salary.setYear(mouthType.getYear());
//                salary.setMonth(mouthType.getMouth());
//                salary.setCreateBy(user.getName());
//                salary.setCreateTime(new Date());
//                salary.setUpdateBy(user.getName());
//                salary.setUpdateTime(new Date());
//                salaryMapper.insertSelective(salary);
//            }
//        });
//    }





}
